package com.xishu.dao.jpa;

import com.xishu.entity.order.DeleteOrderItem;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import javax.persistence.Tuple;
import java.util.List;

public interface DeleteOrderItemDao extends JpaRepository<DeleteOrderItem, Long>, JpaSpecificationExecutor<DeleteOrderItem> {
    /**
     * 计算删除的菜品数量
     *
     * @param shopId
     * @param startTime
     * @param endTime
     * @return
     */
    @Query("select  sum(countNumber) from delete_order_item m where m.shopId =:shopId and m.createTime >= :startTime and m.createTime <= :endTime")
    public Integer countDeleteOrderItem(Long shopId, Long startTime, Long endTime);


    /**
     * 按id集合查询删除的菜品数量总计
     * 总计选项
     *
     * @param
     * @param startTime
     * @param endTime
     * @return
     */
    @Query(value = "select item_id as tmpId,sum(count_number) as value,order_item_name as y,order_item_name_zh as y_zh,order_item_name_en as y_en from delete_order_item m " +
            "where m.shop_id in (:shopIdList) and m.create_time >= :startTime and m.create_time <= :endTime and m.order_item_name_zh in (:deleteItemName_zh) " +
            "group by m.order_item_name_zh " +
            "order by sum(count_number) desc",nativeQuery = true)
    List<Tuple> idDeleteTotalQtyZh(List<Long> shopIdList, Long startTime, Long endTime, List<String> deleteItemName_zh);

    @Query(value = "select item_id as tmpId,sum(count_number) as value,order_item_name as y,order_item_name_zh as y_zh,order_item_name_en as y_en from delete_order_item m " +
            "where m.shop_id in (:shopIdList) and m.create_time >= :startTime and m.create_time <= :endTime and m.order_item_name_en in (:deleteItemName_en) " +
            "group by m.order_item_name_en " +
            "order by sum(count_number) desc", nativeQuery = true)
    List<Tuple> idDeleteTotalQtyEn(List<Long> shopIdList, Long startTime, Long endTime, List<String> deleteItemName_en);


    /**
     * 不带id集合查询删除的菜品数量总计
     * 总计选项
     *
     * @param
     * @param startTime
     * @param endTime
     * @return
     */
    @Query(value = "select item_id as tmpId,sum(count_number) as value,order_item_name as y,order_item_name_zh as y_zh,order_item_name_en as y_en from delete_order_item m " +
            "where m.shop_id in (:shopIdList) and m.create_time >= :startTime and m.create_time <= :endTime " +
            "group by m.item_id " +
            "order by sum(count_number) desc", nativeQuery = true)
    List<Tuple> deleteTotalQty(List<Long> shopIdList, Long startTime, Long endTime);

    /**
     * 带中文名称集合查询删除的菜品金额总计
     * 总计选项
     *
     * @param
     * @param startTime
     * @param endTime
     * @return
     */

    @Query(value = "select item_id as tmpId,sum(price) as value,order_item_name as y,order_item_name_zh as y_zh,order_item_name_en as y_en from delete_order_item m " +
            "where m.shop_id in (:shopIdList) and m.create_time >= :startTime and m.create_time <= :endTime and m.order_item_name_zh in (:deleteItemName_zh) " +
            "group by m.order_item_name_zh " +
            "order by sum(price) desc", nativeQuery = true)
    List<Tuple> idDeleteTotalAmountZh(List<Long> shopIdList, Long startTime, Long endTime, List<String> deleteItemName_zh);

    /**
     * 带英文名称集合查询删除的菜品金额总计
     * @param shopIdList
     * @param startTime
     * @param endTime
     * @param deleteItemName_en
     * @return
     */
    @Query(value = "select item_id as tmpId,sum(price) as value,order_item_name as y,order_item_name_zh as y_zh,order_item_name_en as y_en from delete_order_item m " +
            "where m.shop_id in (:shopIdList) and m.create_time >= :startTime and m.create_time <= :endTime and m.order_item_name_en in (:deleteItemName_en) " +
            "group by m.order_item_name_en " +
            "order by sum(price) desc", nativeQuery = true)
    List<Tuple> idDeleteTotalAmountEn(List<Long> shopIdList, Long startTime, Long endTime, List<String> deleteItemName_en);


    /**
     * 不带id集合查询删除的菜品金额总计
     * 总计选项
     *
     * @param
     * @param startTime
     * @param endTime
     * @return
     */
    @Query(value = "select item_id as tmpId,sum(price) as value,order_item_name as y,order_item_name_zh as y_zh,order_item_name_en as y_en from delete_order_item m " +
            "where m.shop_id in (:shopIdList) and m.create_time >= :startTime and m.create_time <= :endTime " +
            "group by m.item_id " +
            "order by sum(price) desc", nativeQuery = true)
    List<Tuple> deleteTotalAmount(List<Long> shopIdList, Long startTime, Long endTime);


    //年月日选项，带菜品id的金额查询
    @Query(value = "select FROM_UNIXTIME(create_time / 1000,:timeFormat ) as x ,order_item_name as y ,order_item_name_zh as y_zh,order_item_name_en as y_en ,item_id as tmpId ,sum(price) as value from delete_order_item m " +
            "where m.shop_id in (:shopIdList) and m.create_time >= :startTime and m.create_time <= :endTime and m.order_item_name_zh in (:deleteItemName_zh) " +
            "group by m.order_item_name_zh ,FROM_UNIXTIME(create_time / 1000,:timeFormat) " +
            "order by sum(price) desc", nativeQuery = true)
    List<Tuple> idDeleteTotalAmountReportZh(List<Long> shopIdList, Long startTime, Long endTime, String timeFormat, List<String> deleteItemName_zh);

    @Query(value = "select FROM_UNIXTIME(create_time / 1000,:timeFormat ) as x ,order_item_name as y ,order_item_name_zh as y_zh,order_item_name_en as y_en ,item_id as tmpId ,sum(price) as value from delete_order_item m " +
            "where m.shop_id in (:shopIdList) and m.create_time >= :startTime and m.create_time <= :endTime and m.order_item_name_en in (:deleteItemName_en) " +
            "group by m.order_item_name_en ,FROM_UNIXTIME(create_time / 1000,:timeFormat) " +
            "order by sum(price) desc", nativeQuery = true)
    List<Tuple> idDeleteTotalAmountReportEn(List<Long> shopIdList, Long startTime, Long endTime, String timeFormat, List<String> deleteItemName_en);



    //年月日选项，带菜品id的数量查询
    @Query(value = "select FROM_UNIXTIME(create_time / 1000,:timeFormat ) as x ,order_item_name as y ,order_item_name_zh as y_zh,order_item_name_en as y_en ,item_id as tmpId ,sum(count_number) as value from delete_order_item m " +
            "where m.shop_id in (:shopIdList) and m.create_time >= :startTime and m.create_time <= :endTime and m.order_item_name_zh in (:deleteItemName_zh) " +
            "group by m.order_item_name_zh ,FROM_UNIXTIME(create_time / 1000,:timeFormat) " +
            "order by sum(count_number) desc", nativeQuery = true)
    List<Tuple> idDeleteTotalQtyReportZh(List<Long> shopIdList, Long startTime, Long endTime, String timeFormat, List<String> deleteItemName_zh);

    @Query(value = "select FROM_UNIXTIME(create_time / 1000,:timeFormat ) as x ,order_item_name as y ,order_item_name_zh as y_zh,order_item_name_en as y_en ,item_id as tmpId ,sum(count_number) as value from delete_order_item m " +
            "where m.shop_id in (:shopIdList) and m.create_time >= :startTime and m.create_time <= :endTime and m.item_id in (:deleteItemName_en) " +
            "group by m.order_item_name_en ,FROM_UNIXTIME(create_time / 1000,:timeFormat) " +
            "order by sum(count_number) desc", nativeQuery = true)
    List<Tuple> idDeleteTotalQtyReportEn(List<Long> shopIdList, Long startTime, Long endTime, String timeFormat,List<String> deleteItemName_en);


    //年月日选项，不带菜品id的金额查询
    @Query(value = "select FROM_UNIXTIME(create_time / 1000,:timeFormat ) as x ,order_item_name as y ,order_item_name_zh as y_zh,order_item_name_en as y_en ,item_id as tmpId ,sum(price) as value from delete_order_item m " +
            "where m.shop_id in (:shopIdList) and m.create_time >= :startTime and m.create_time <= :endTime " +
            "group by m.order_item_name ,FROM_UNIXTIME(create_time / 1000,:timeFormat)" +
            "order by sum(price) desc", nativeQuery = true)
    List<Tuple> deleteTotalAmountReport(List<Long> shopIdList, Long startTime, Long endTime, String timeFormat);


    //年月日选项，不带菜品id的数量查询
    @Query(value = "select FROM_UNIXTIME(create_time / 1000,:timeFormat ) as x ,order_item_name as y ,order_item_name_zh as y_zh,order_item_name_en as y_en ,item_id as tmpId ,sum(count_number) as value from delete_order_item m " +
            "where m.shop_id in (:shopIdList) and m.create_time >= :startTime and m.create_time <= :endTime " +
            "group by m.order_item_name ,FROM_UNIXTIME(create_time / 1000,:timeFormat) " +
            "order by sum(count_number) desc", nativeQuery = true)
    List<Tuple> deleteTotalQtyReport(List<Long> shopIdList, Long startTime, Long endTime, String timeFormat);

    @Query(value = "select order_item_name_zh as name_zh,order_item_name_en as name_en from delete_order_item m " +
            "where m.shop_id =:shopId group by order_item_name_zh, order_item_name_en", nativeQuery = true)
    public List<Tuple> shopSearchItem(Long shopId);
}
